package com.itheima.dao.impl;

import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    //查询
    @Override
    public List<User> findAll() {
        //定义集合, 存储数据
        List<User> userList = new ArrayList<>();
        Connection conn = null;
        ResultSet rs = null;
        Statement st = null;
        try {
            //1. 获取连接
            conn = JDBCUtils.getConnection();
            //2. 编写SQL
            String sql = "SELECT * FROM `user`";
            //3. 获取语句执行者对象
            st = conn.createStatement();
            //4. 执行SQL, 返回结果集
            rs = st.executeQuery(sql);
            while (rs.next()) {
                // 创建对象
                // 获取每一个字段的值
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                String qq = rs.getString("qq");
                String email = rs.getString("email");
                // 创建一个User对象, 封装每一行数据
                User user = new User(id, name, gender, age, address, qq, email);

                userList.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn, st, rs);
        }

        return userList;
    }


    //添加
    @Override
    public void save(User user) {
        Connection conn = null;
        Statement st = null;

        try {
            //1. 获取连接
            conn = JDBCUtils.getConnection();
            //2. 编写SQL
            String sql = "insert into user values(null,'" + user.getName() + "','" + user.getGender() + "','" + user.getAge() + "','" + user.getAddress() + "','" + user.getQq() + "','" + user.getEmail() + "',null,null)";
            //3. 获取语句执行者对象
            st = conn.createStatement();
            //执行保存
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn, st);
        }

    }

    //删除
    @Override
    public void deleteById(String id) {
        Connection conn = null;
        Statement st = null;

        try {
            //1. 获取连接
            conn = JDBCUtils.getConnection();
            //2. 编写SQL
            String sql = "delete from user where id =" + id;
            //3. 获取语句执行者对象
            st = conn.createStatement();
            //执行保存
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn, st);
        }
    }

    //修改
    @Override
    public User findById(String findId) {
        Connection conn = null;
        ResultSet rs = null;
        Statement st = null;
        User user = null;
        try {
            //1. 获取连接
            conn = JDBCUtils.getConnection();
            //2. 编写SQL
            String sql = "select * from user where id =" + findId;
            //3. 获取语句执行者对象
            st = conn.createStatement();
            //4. 执行SQL, 返回结果集
            rs = st.executeQuery(sql);
            while (rs.next()) {
                // 创建对象
                // 获取每一个字段的值
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                String qq = rs.getString("qq");
                String email = rs.getString("email");
                // 创建一个User对象, 封装每一行数据
                user = new User(id, name, gender, age, address, qq, email);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn, st, rs);
        }

        return user;
    }

    @Override
    public void update(User user) {
        Connection conn = null;
        Statement st = null;

        try {
            //1. 获取连接
            conn = JDBCUtils.getConnection();
            //2. 编写SQL
            String sql = "update user set name = '" + user.getName() + "',gender = '" + user.getGender() + "' ,age = " + user.getAge() + " , address = '" + user.getAddress() + "' , qq = '" + user.getQq() + "', email = '" + user.getEmail() + "' where id = " + user.getId();
            //3. 获取语句执行者对象
            st = conn.createStatement();
            //执行保存
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn, st);
        }
    }
}
